<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
    
    class User_m extends CI_Model {
        public function __construct() {
            $this->load->database();
        }
        
        function login($idno, $password) {
            $this->db->select('idno,fname,mname,lname,email,password');
            $this->db->where('idno', $idno);
            $this->db->where('password', sha1($password));
            $query = $this->db->get('user');
                        
            if ($query->num_rows < 1) {
                $info = NULL;
            } else {
                foreach ($query->result() as $row) {
                   $info = $row->idno;
                }
            }
            return $info;
        }
        
        function generate_password() {
            $chars = 'abcdefghijkmnopqrstuvwxyz023456789';
            $count = mb_strlen($chars);

            for ($i = 0, $result = ''; $i < 8; $i++) {
                $index = rand(0, $count - 1);
                $result .= mb_substr($chars, $index, 1);
            }
            return $result;
        }
        
        function send_email($to, $from, $subject, $message) {
            $config = array(
                'protocol' => 'smtp',
                'smtp_host' => 'ssl://smtp.googlemail.com',
                'smtp_port' => 465,
                'smtp_user' => 'scisinfoboard@gmail.com',
                'smtp_pass' => 'SLU-SCIS-info-board2013',
                'mailtype' => 'html', 
                'charset'   => 'iso-8859-1'
            );

            $this->load->library('email', $config);

            $this->email->set_newline("\r\n");
            $this->email->from($from, 'SLU-SCIS Info Board Management');
            $this->email->to($to);
            $this->email->subject($subject);
            $this->email->message($message);

            if($this->email->send()) {
                return TRUE;
            }
            else {
                return FALSE;
            }
        }
        
        function insert_stud($one_account, $users) {
            $this->load->helper('url');
            if($one_account) {
              $password = $this->generate_password();
              $data = array(
                  'idno' => $users['idno'],
                  'lname' => $users['lname'],
                  'fname' => $users['fname'],
                  'mname' => $users['mname'],
                  'email' => $users['email'],
                  'password' => sha1($password),
                  'course' => $users['course'],
                  'type' => 'Student',
                  'status' => 'active'
              );

              if($this->user_exists($data['idno'])) {
                echo '<h2>The ID Number is already registered in the database.</h2>';
              } else {
                $query = $this->db->insert('user', $data);

                $user_id = $this->db->insert_id();//get newly inserted user_id from the database

                $role = $this->role_m->get_role_id("viewer");
                $user_role = array(
                    'user_id' => $user_id,
                    'role_id' => $role->role_id
                );

                $query2 = $this->db->insert('user_role', $user_role);

                $to = $data['email'];
                $from = "SLU-SCIS Info Board Management";
                $subject = "Saint Louis University - SCIS Info Board";
                $message = "<h1>Welcome to the SLU-SCIS Info Board!</h1><br/>
                  <p>You can now log in to the site using the following:<br/>
                  <h3>Username: " . $data['idno'] . "<br/>
                  Password: " . $password . "</h3><br/>
                  <a href=infoboard.slu.edu.ph>SLU-SCIS Info Board Homepage</a></p>";
                $this->send_email($to, $from, $subject, $message);
              }
            } else {//multiple accounts
              for($x = 0; $x < sizeof($users); $x++) {
                $password = $this->generate_password();

                $data = array(
                    'idno' => $users[$x][0],
                    'lname' => $users[$x][1],
                    'fname' => $users[$x][2],
                    'mname' => $users[$x][3],
                    'email' => $users[$x][4],
                    'password' => sha1($password),
                    'course' => $users[$x][5],
                    'type' => 'Student',
                    'status' => 'active'
                );

                $query = $this->db->insert('user', $data);

                $user_id = $this->db->insert_id();//get newly inserted user_id from the database

                $role = $this->role_m->get_role_id("viewer");
                $user_role = array(
                    'user_id' => $user_id,
                    'role_id' => $role->role_id
                );

                $query2 = $this->db->insert('user_role', $user_role);

                $to = $data['email'];
                $from = "SLU-SCIS Info Board Management";
                $subject = "Saint Louis University - SCIS Info Board";
                $message = "<h1>Welcome to the SLU-SCIS Info Board!</h1><br/>
                  <p>You can now log in to the site using the following:<br/>
                  <h3>Username: " . $data['idno'] . "<br/>
                  Password: " . $password . "</h3><br/>
                  <a href=infoboard.slu.edu.ph>SLU-SCIS Info Board Homepage</a></p>";
                $this->send_email($to, $from, $subject, $message);
              }
            }
        }
        
        function insert_emp($one_account, $users) {
            $this->load->helper('url');
            if($one_account) {
              $password = $this->generate_password();
              $data = array(
                  'idno' => $users['idno'],
                  'lname' => $users['lname'],
                  'fname' => $users['fname'],
                  'mname' => $users['mname'],
                  'email' => $users['email'],
                  'password' => sha1($password),
                  'department' => $users['dept'],
                  'type' => 'Employee',
                  'status' => 'active'
              );

              if($this->user_exists($data['idno'])) {
                echo '<h2>The ID Number is already registered in the database.</h2>';
              } else {
                $query = $this->db->insert('user', $data);

                $user_id = $this->db->insert_id();//get newly inserted user_id from the database

                $role = $this->role_m->get_role_id("viewer");
                $user_role = array(
                    'user_id' => $user_id,
                    'role_id' => $role->role_id
                );

                $query2 = $this->db->insert('user_role', $user_role);

                $to = $data['email'];
                $from = "SLU-SCIS Info Board Management";
                $subject = "Saint Louis University - SCIS Info Board";
                $message = "<h1>Welcome to the SLU-SCIS Info Board!</h1><br/>
                  <p>You can now log in to the site using the following:<br/>
                  <h3>Username: " . $data['idno'] . "<br/>
                  Password: " . $password . "</h3><br/>
                  <a href=infoboard.slu.edu.ph>SLU-SCIS Info Board Homepage</a></p>";
                $this->send_email($to, $from, $subject, $message);
              }
            } else {//multiple accounts
              for($x = 0; $x < sizeof($users); $x++) {
                $password = $this->generate_password();
                $data = array(
                    'idno' => $users[$x][0],
                    'lname' => $users[$x][1],
                    'fname' => $users[$x][2],
                    'mname' => $users[$x][3],
                    'email' => $users[$x][4],
                    'password' => sha1($password),
                    'department' => $users[$x][5],
                    'type' => 'Employee',
                    'status' => 'active'
                );

                $query = $this->db->insert('user', $data);

                $user_id = $this->db->insert_id();//get newly inserted user_id from the database

                $role = $this->role_m->get_role_id("viewer");
                $user_role = array(
                    'user_id' => $user_id,
                    'role_id' => $role->role_id
                );

                $query2 = $this->db->insert('user_role', $user_role);

                $to = $data['email'];
                $from = "SLU-SCIS Info Board Management";
                $subject = "Saint Louis University - SCIS Info Board";
                $message = "<h1>Welcome to the SLU-SCIS Info Board!</h1><br/>
                  <p>You can now log in to the site using the following:<br/>
                  <h3>Username: " . $data['idno'] . "<br/>
                  Password: " . $password . "</h3><br/>
                  <a href=infoboard.slu.edu.ph>SLU-SCIS Info Board Homepage</a></p>";
                $this->send_email($to, $from, $subject, $message);
              }
            }
        }
        
        function insert_admin($info) {
            $this->load->helper('url');
            $password = $this->generate_password();
            $data = array(
                'idno' => $info['idno'],
                'lname' => $info['lname'],
                'fname' => $info['fname'],
                'mname' => $info['mname'],
                'email' => $info['email'],
                'password' => sha1($password),
                'type' => 'Administrator',
                'status' => 'active'
            );
            if($this->user_exists($data['idno'])) {
              echo '<h2>The ID Number is already registered in the database.</h2>';
            } else {
              $query = $this->db->insert('user', $data);
              $user_id = $this->db->insert_id();//get newly inserted user_id from the database
              $role = $this->role_m->get_role_id("admin");
              $user_role = array(
                  'user_id' => $user_id,
                  'role_id' => $role->role_id
              );

              $query2 = $this->db->insert('user_role', $user_role);

              $to = $data['email'];
              $from = "SLU-SCIS Info Board Management";
              $subject = "Saint Louis University - SCIS Info Board";
              $message = "<h1>Welcome to the SLU-SCIS Info Board!</h1><br/>
                <p>You can now log in to the site using the following:<br/>
                <h3>Username: " . $data['idno'] . "<br/>
                Password: " . $password . "</h3><br/>
                <p>You are registered as an administrator of the SCIS Info Board.<br/>
                <a href=infoboard.slu.edu.ph>SLU-SCIS Info Board Homepage</a></p>";
              $this->send_email($to, $from, $subject, $message);
            }
        }
        
        function update_user($data) {
            if(is_null($data['password'])) {
              $new = array(
                  'fname' => $data['fname'],
                  'mname' => $data['mname'],
                  'lname' => $data['lname'],
                  'email' => $data['email'],
                  'course' => $data['course'],
                  'department' => $data['department']
              );
              $this->db->where('idno', $data['idno']);
              $this->db->update('user', $new);
            } else {
              $new = array(
                  'fname' => $data['fname'],
                  'mname' => $data['mname'],
                  'lname' => $data['lname'],
                  'email' => $data['email'],
                  'password' => $data['password'],
                  'course' => $data['course'],
                  'department' => $data['department']
              );
              $this->db->where('idno', $data['idno']);
              $this->db->update('user', $new);
            }
        }
        
        function reset_password($idno, $email) {
            $password = $this->generate_password();

            $data = array('password' => sha1($password));

            $this->db->where('idno', $idno);
            $this->db->update('user', $data);

            $to = $email;
            $from = "SLU-SCIS Info Board Management";
            $subject = "Request for New Password";
            $message = "<h1>Your password to SLU-SCIS Info Board has been reset</h1><br/>
              <p>You can now log in to the site using the following:<br/>
              <h3>Username: " . $idno . "<br/>
              Your New Password: " . $result . "</h3><br/>
              <a href=infoboard.slu.edu.ph>SLU-SCIS Info Board Homepage</a></p>";
            if($this->send_email($to, $from, $subject, $message)) {
              return TRUE;
            } else {
              return FALSE;
            }
        }
        
        function get_all_users() {             
            $this->db->select('*');
            $this->db->from('user');
            $this->db->join('user_role', 'user.user_id = user_role.user_id');
            $this->db->join('role', 'user_role.role_id = role.role_id');
            $query = $this->db->get();

            return $query->result_array();
        }
        
        function user_exists($idno) {
            $this->db->select('idno');
            $this->db->where('idno', $idno);
            $query = $this->db->get('user');
            $result = $query->result();
            $exists = TRUE;
            if(empty($result)) {
                $exists = FALSE;
            }

            return $exists;
        }
        
        function get_user($idno) {
            $this->db->select('idno, fname, mname, lname, email, password, course, department');
            $this->db->where('idno', $idno);
            $query = $this->db->get('user');
            $result = $query->row_array();
            return $result;
        }
        
        function delete_user ($idno){
            $this->db->where('idno', $idno);
            $this->db->set('status', 'deleted');
            $this->db->update('user');
        }
        
        function update_user_role($user_id, $role_id) {
            $data = array(
               'role_id' => $role_id
            );

            $this->db->where('user_id', $user_id);
            $this->db->update('user_role', $data); 
        }
        
        function change_password($idno, $password){
            $this->db->where('idno', $idno);
            $this->db->set('password', sha1($password));
            $this->db->update('user');
        }
        
        function get_old_pass($idno){
            $this->db->where('idno', $idno);
            $query = $this->db->get('user');
            return $query->row_array();
        }
        
        function get_all_departments() {
            $query = "SELECT DISTINCT department
                      FROM user
                      WHERE department IS NOT null
                      ORDER BY department ASC";
            $qq = $this->db->query($query);
            return $qq->result_array();
        }
        
        function get_users_in_dept($dept) {
            $this->db->from('user');
            $this->db->where('department', $dept);
            $query = $this->db->get();
            
            return $query->result_array();
        }
        
        function admin_user_records($idno) {
            $this->db->select('*');
            $this->db->from('user');
            $this->db->join('user_role', 'user.user_id = user_role.user_id');
            $this->db->join('role', 'user_role.role_id = role.role_id');
            $this->db->where('idno !=', $idno);
            $query = $this->db->get();

            return $query->result_array();
       }

        function fetch_users_asc($limit, $start, $idno, $column) {
            $this->db->select('*');
            $this->db->from('user');
            $this->db->join('user_role', 'user.user_id = user_role.user_id');
            $this->db->join('role', 'user_role.role_id = role.role_id');
            $this->db->where('idno !=', $idno);
            $this->db->limit($limit, $start);
            $this->db->order_by($column, 'ASC');
            $query = $this->db->get();

            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
       }
       
       function fetch_users_desc($limit, $start, $idno, $column) {
            $this->db->select('*');
            $this->db->from('user');
            $this->db->join('user_role', 'user.user_id = user_role.user_id');
            $this->db->join('role', 'user_role.role_id = role.role_id');
            $this->db->where('idno !=', $idno);
            $this->db->limit($limit, $start);
            $this->db->order_by($column, 'DESC');
            $query = $this->db->get();

            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            return false;
       }
       
       function get_user_id($idno) {
          $query = $this->db->query("SELECT user_id from user where idno='".$idno."'");
          return $query->row();
       }
    }
?>
